<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mreportuser extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
	public function getListUser($number = 0,$offset = 0)
    {
        $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT * FROM user";
        
        $sql .= " ORDER BY created DESC";
        $count = $this->db->query($sql)->num_rows();
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }
    
    public function addUser($data)
    {//echo "<pre>"; die(print_r($data));
        $check = $this->db->query("SELECT id FROM user WHERE username = '".$data['username']."'")->num_rows();
        
        if($check == 0){
            $this->db->insert('user', $data);
            return 200;
        }
        
        return 201;
    }
    
    public function getUserInfo($id)
    {
        return $this->db->query("SELECT *,DATE_FORMAT(dateofbirth,'%d-%m-%Y') AS dateofbirth FROM user WHERE id = ".$id)->row_object(); 
    }
    
    public function editUser($id, $data)
    {
        $this->db->update('user', $data, "id = ".$id);
        
        return 200;
    }
    
    public function deleteUser($id)
    {
        $this->db->delete('user', "id = ".$id);
        
        return 200;
    }
    
    public function sendMail($data)
    {
        $id=$this->session->userdata['admin_id'];  //id người gửi
        $sender=$this->db->query("SELECT * FROM user WHERE user.id = '$id'")->row()->fullname;
        $subject=$this->getParamString("subject_email");  //Tiêu đề
        $content=$this->getParamString("content"); //Nội dung
        $user=$this->getParamString("user");   //NGười nhận
        $config = array(
						'protocol' => 'smtp',
						'smtp_host' => $this->_smtp_host,
						'smtp_user' => $this->_smtp_user,
						'smtp_pass' => $this->_smtp_pass,
						'mailtype' => 'html'
                    );
        $this->load->library('email');
        $this->email->initialize($config);
        $body= "Bạn mới nhận được tin nhắn từ admin :   ". $sender."<br>";
        $body.=$content;
        $this->email->from($this->_smtp_user,$sender);
        $this->email->to($user);  
        $this->email->subject($subject);
        $this->email->message($body);    
        $a=$this->email->send();
    }
    
//    public function sendMail($data)
//    {
//        $config = array(
//						'protocol' => 'smtp',
//						'smtp_host' => $this->_smtp_host,
//						'smtp_user' => $this->_smtp_user,
//						'smtp_pass' => $this->_smtp_pass,
//						'mailtype' => 'html'
//                    );
//        $this->load->library('email');
//        $this->email->initialize($config);
//        $body= "Bạn mới được tạo tài khoản từ trang quản lí project của Wicity "."<br>";
//        $body.= "Tên tài khoản: ".$data['username']."<br>";
//        $body.= "Password: ".$this->getParamString('password')."<br>";
//        $body.= "Link đăng nhập: "."<a href='".site_url()."'>".site_url()."</a>"."<br>";
//     
//        $this->email->from($this->_smtp_user, 'Wicity');
//        $this->email->to($data['email']);  
//        $this->email->subject("Bạn đã được tạo 1 tài khoản từ Wicity project");
//        $this->email->message($body);    
//        
//        $a=$this->email->send();
//    }
    public function getListProject($number = 0,$offset = 0)
    {
        if($this->session->userdata("level") != 1)  //Nếu không phải là admin thì nhảy vào của chính nó.
        {
            $user_id=$this->session->userdata("admin_id");
        }
        else{
            $user_id=$this->getParamString("us");   //Nếu là admin thì nhảy vào chính xác
        }
            $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT p.*,DATE_FORMAT(p.created,'%d/%m/%Y %T') as created1,DATE_FORMAT(p.time_start,'%d/%m/%Y %T') as start,DATE_FORMAT(p.time_end,'%d/%m/%Y %T') as end,u1.username as user_created,GROUP_CONCAT(u.username SEPARATOR ', ') as users
                FROM project as p
                INNER JOIN project_key as pk ON p.id = pk.project_id 
                INNER JOIN user as u ON pk.user_id = u.id
                INNER JOIN user as u1 ON u1.id = p.user_id
                WHERE p.active = 1
                AND  pk.user_id = '$user_id'
                GROUP BY p.id    
                ";
   ;
        $sql .= " ORDER BY p.created DESC";
        $count = $this->db->query($sql)->num_rows();
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        $user=$this->db->query("SELECT * FROM user WHERE id = '$user_id'")->row();
        return array('data' => $xs, 'count' => $count,'user'=>$user);
    }
    public function getProjectDetail($id){
                if($this->session->userdata("level") == 1)
                {
                    $user_id=$this->getParamString ("us");          //Nếu là admin thì lấy id là tham số url 
                }else{
                    $user_id=$this->session->userdata("admin_id");  //Nếu là user thì lấy ngay thống kế chính nó
                }
		$data = $this->db->query('SELECT p.*, u.fullname, u.username FROM project AS p INNER JOIN user AS u ON u.id = p.user_id WHERE p.id = '.$id.' ')->row_object();
               
		$data->project_user = $this->db->query('SELECT u.id, u.username, u.fullname FROM user AS u INNER JOIN project_key AS pk ON u.id = pk.user_id WHERE pk.project_id = '.$data->id.' ')->result_object();
//		if( $this->session->userdata('level') == 1 ){
//		
                //Đoạn này chỉ lấy module của user đang thống kê
			$module = $this->db->query('SELECT module.* FROM module INNER JOIN module_key ON module.id=module_key.module_id WHERE module_key.user_id = '.$user_id.' AND module.project_id = '.$data->id.' GROUP BY module.id ORDER BY module.created ASC ')->result_object();
		
//		}else{    //CHO XEM TẤT CẢ CAC MODULE TRONG PROJECT
//			if( $this->session->userdata('level') == 2 ){
//		//THIẾU DUYỆT PROJECT		$module = $this->db->query('SELECT m.* FROM module AS m INNER JOIN module_key AS mk ON m.id = mk.module_id WHERE mk.user_id = '.$this->session->userdata('admin_id').' ORDER BY m.created DESC')->result_object();
//			}
//		}
//       echo "<pre>"; var_dump($module);die;        
		foreach ( $module AS $el1=>$le1 ){
			$module[$el1]->module_user = $this->db->query('SELECT u.fullname,u.username,u.id,mk.rate FROM module_key AS mk INNER JOIN user AS u ON u.id = mk.user_id WHERE mk.module_id = '.$le1->id.' ')->result_object();
			$module[$el1]->comment=$this->db->query("SELECT p.*,u.fullname FROM module_key_comment as p INNER JOIN user as u ON p.user_id=u.id WHERE p.module_id = '$le1->id' ORDER BY p.created DESC ")->result_object();
		$module[$el1]->reason=$this->db->query("SELECT * FROM module_key_reason as m WHERE m.module_id='$le1->id' ORDER BY m.created DESC LIMIT 1 ")->row()->reason;
			$total_rate1 = $this->db->query('SELECT SUM(rate) AS rate FROM module_key WHERE module_id = '.$le1->id.' ')->row_object()->rate;
			$num_rate1 = $this->db->query('SELECT id FROM module_key WHERE module_id = '.$le1->id.' ')->num_rows();
			$module[$el1]->percent = ceil($total_rate1/$num_rate1);
			
			
		}
                
                        $data->module = $module;
			$total_rate = $this->db->query('SELECT SUM(module_key.rate) AS rate FROM module_key INNER JOIN module ON module.id=module_key.module_id WHERE module.project_id = '.$data->id.' ')->row_object()->rate;
			$num_rate = $this->db->query('SELECT * FROM module WHERE module.project_id = '.$data->id.' ')->num_rows();
//                        var_dump("SELECT module_key.id FROM module_key INNER JOIN module ON module.id=module_key.module_id WHERE module.project_id = ".$data->id);die;
			$data->percent = ceil($total_rate/$num_rate);
                        
                        //Đoạn này lấy phần trăm trung bình của user đang thống kê
                        $data->avg_user=$this->db->query("SELECT SUM(mk.rate)/COUNT(mk.rate) as avg FROM module_key as mk WHERE mk.project_id= '$id' AND mk.user_id = '$user_id'")->row()->avg;
                        $data->avg_user=round($data->avg_user);
                        //Đoạn này lấy tên của user thống kê
                        $data->user_report=$this->db->query("SELECT * FROM user WHERe id = '$user_id'")->row();
            $data->time_percent = ceil( (time()- strtotime($data->time_start))/(strtotime($data->time_end) - strtotime($data->time_start)) )*100;
//		var_dump($data);die;
		return $data;
	}
    public function getProcess($id)
    {
        if($this->session->userdata("level") == 1)
        {
                    $user_id=$this->getParamString ("us");          //Nếu là admin thì lấy id là tham số url 
        }else{
                    $user_id=$this->session->userdata("admin_id");  //Nếu là user thì lấy ngay thống kế chính nó
        }
        
        //Lấy date hiện tại
        $now=date('Y/m/d');
        $result=$this->db->query("SELECT DATE_FORMAT(date_save,'%d/%m/%Y') as date,rate_avg as rate FROM avg_user_project WHERE user_id = '$user_id' AND project_id= '$id' AND DATE_FORMAT(date_save,'%Y/%m/%d') <= '$now' ORDER BY date asc " )->result_object();
        
        return $result;
    }
    
    public function getUserDetail($id)
    {
        return $this->db->query("SELECT * FROM user WHERE id = '$id'")->row();
    }
}
